﻿Imports System.Data.SqlClient
Imports Entities
Public Class ConectadoSQL
    Private conexionBiblioteca As SqlConnection

    Private Const SERVER As String = "ACER"
    Private Const DB As String = "Biblioteca"
    Private historicos As List(Of Prestamo)
    Private listaPrestamos As List(Of Prestamo)


#Region "Constructores_y_Abrir_y_Cerrar_Conexion"
    Public Sub New()
        conexionBiblioteca = New SqlConnection
        conexionBiblioteca.ConnectionString = "Data Source=" & SERVER & ";Initial Catalog=" & DB & ";Integrated Security = True;"
        historicos = getPrestamos.ToList
        listaPrestamos = getPrestamos.ToList
    End Sub

    Private Sub inicializarListaPrestamos()
        AbrirConexion()
        listaPrestamos = getPrestamos.ToList
        CerrarConexion()
    End Sub

    Private Function AbrirConexion() As String
        Try
            If conexionBiblioteca.State = ConnectionState.Closed Then
                conexionBiblioteca.Open()
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return ""
    End Function
    Private Function CerrarConexion() As String
        Try
            If conexionBiblioteca.State = ConnectionState.Open Then
                conexionBiblioteca.Close()
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return ""
    End Function
#End Region


    'obtener los autroes de la tabla libro de la base de datos BIBLIOTECA
    Public Function GetLibros() As ObjectModel.ReadOnlyCollection(Of Libro)
        AbrirConexion()
        Dim listaLibros As New List(Of Libro)

        Dim sqlSelectLibros = "SELECT * FROM Libro"
        Dim commandoGetLibros As New SqlCommand(sqlSelectLibros, conexionBiblioteca)

        Dim dataReaderLibros As SqlDataReader = commandoGetLibros.ExecuteReader()

        While dataReaderLibros.Read
            listaLibros.Add(New Libro(dataReaderLibros("Isbn"), dataReaderLibros("CodCategoria"), Integer.Parse(dataReaderLibros("CodSoporte")), dataReaderLibros("Titulo"), dataReaderLibros("Ejemplares")))
        End While
        CerrarConexion()
        Return listaLibros.AsReadOnly
    End Function

    'obtener los autroes de la tabla autores de la base de datos BIBLIOTECA
    Public Function GetAutores() As ObjectModel.ReadOnlyCollection(Of Autor)
        AbrirConexion()
        Dim listaAutores As New List(Of Autor)

        Dim sqlSelectAutores = "SELECT * FROM Autor"
        Dim commandoGetAutores As New SqlCommand(sqlSelectAutores, conexionBiblioteca)

        Dim dataReaderAutores As SqlDataReader = commandoGetAutores.ExecuteReader()

        While dataReaderAutores.Read
            listaAutores.Add(New Autor(dataReaderAutores("CodAutor"), dataReaderAutores("Nombre"), dataReaderAutores("Apellidos")))
        End While
        CerrarConexion()
        Return listaAutores.AsReadOnly
    End Function

    'obtener los autroes de la tabla Categoria de la base de datos BIBLIOTECA
    Public Function GetCategorias() As ObjectModel.ReadOnlyCollection(Of Categoria)
        AbrirConexion()
        Dim listaCategorias As New List(Of Categoria)

        Dim sqlSelectCategoria = "SELECT * FROM Categoria"
        Dim commandoGetCategorias As New SqlCommand(sqlSelectCategoria, conexionBiblioteca)

        Dim dataReaderCategorias As SqlDataReader = commandoGetCategorias.ExecuteReader()

        While dataReaderCategorias.Read
            listaCategorias.Add(New Categoria(dataReaderCategorias("CodCategoria"), dataReaderCategorias("Nombre")))
        End While
        CerrarConexion()
        Return listaCategorias.AsReadOnly
    End Function

    'obtener los autroes de la tabla Clientes de la base de datos BIBLIOTECA
    Public Function GetClientes() As ObjectModel.ReadOnlyCollection(Of Cliente)
        AbrirConexion()
        Dim listaClientes As New List(Of Cliente)

        Dim sqlSelectCliente = "SELECT * FROM Cliente"
        Dim commandoGetCliente As New SqlCommand(sqlSelectCliente, conexionBiblioteca)

        Dim dataReaderClientes As SqlDataReader = commandoGetCliente.ExecuteReader()

        While dataReaderClientes.Read
            listaClientes.Add(New Cliente(dataReaderClientes("Dni"), dataReaderClientes("Nombre"), dataReaderClientes("Apellidos"), dataReaderClientes("Telefono")))
        End While
        CerrarConexion()
        Return listaClientes.AsReadOnly
    End Function
    Public Function getPrestamosHistoricos() As ObjectModel.ReadOnlyCollection(Of Prestamo)
        AbrirConexion()
        Dim listaPrestamo As New List(Of Prestamo)

        Dim sqlSelectPrestamo = "SELECT * FROM Prestamo"
        Dim commandoGetPrestamo As New SqlCommand(sqlSelectPrestamo, conexionBiblioteca)

        Dim dataReaderPrestamo As SqlDataReader = commandoGetPrestamo.ExecuteReader()

        While dataReaderPrestamo.Read

            listaPrestamo.Add(New Prestamo(dataReaderPrestamo("Isbn"), dataReaderPrestamo("Dni"), dataReaderPrestamo("FechaPrestamo"), dataReaderPrestamo("FechaDevolucion"), dataReaderPrestamo("Entregado")))

            listaPrestamo.Add(New Prestamo(dataReaderPrestamo("Isbn"), dataReaderPrestamo("Dni"), dataReaderPrestamo("FechaPrestamo"), dataReaderPrestamo("FechaDevolucion"), dataReaderPrestamo("Entregado")))

        End While
        CerrarConexion()
        Return listaPrestamo.AsReadOnly
    End Function
    'obtener los prestamo de la tabla autores de la base de datos BIBLIOTECA
    Public Function getPrestamos() As ObjectModel.ReadOnlyCollection(Of Prestamo)
        AbrirConexion()
        Dim listaPrestamo As New List(Of Prestamo)

        Dim sqlSelectPrestamo = "SELECT * FROM Prestamo WHERE Prestamo.Entregado=0"
        Dim commandoGetPrestamo As New SqlCommand(sqlSelectPrestamo, conexionBiblioteca)

        Dim dataReaderPrestamo As SqlDataReader = commandoGetPrestamo.ExecuteReader()

        While dataReaderPrestamo.Read

            listaPrestamo.Add(New Prestamo(dataReaderPrestamo("Isbn"), dataReaderPrestamo("Dni"), dataReaderPrestamo("FechaPrestamo"), dataReaderPrestamo("FechaDevolucion"), dataReaderPrestamo("Entregado")))

        End While
        CerrarConexion()
        Return listaPrestamo.AsReadOnly
    End Function

    'obtiene los soportes en el que esta redactada la novela
    Public Function GetSoportes() As ObjectModel.ReadOnlyCollection(Of Soporte)
        AbrirConexion()
        Dim listaSoporte As New List(Of Soporte)

        Dim sqlSelectPrestamo As String = "SELECT * FROM Soporte"
        Dim commandoGetPrestamo As New SqlCommand(sqlSelectPrestamo, conexionBiblioteca)

        Dim dataReaderPrestamo As SqlDataReader = commandoGetPrestamo.ExecuteReader()

        While dataReaderPrestamo.Read
            listaSoporte.Add(New Soporte(dataReaderPrestamo("CodSoporte"), dataReaderPrestamo("Nombre")))
        End While
        CerrarConexion()
        Return listaSoporte.AsReadOnly
    End Function

    Public Function getLibrosPorCategoria(categoria As Categoria) As ObjectModel.ReadOnlyCollection(Of Libro)
        AbrirConexion()
        Dim listaLibrosPorCategoria As New List(Of Libro)

        Dim sqlSelectLibroPorCategoria As String = "SELECT * FROM Libro INNER JOIN Categoria ON Libro.CodCategoria = Categoria.CodCategoria Where Categoria.Nombre = '" & categoria.Nombre & "'"

        Dim commandoGetLibros As New SqlCommand(sqlSelectLibroPorCategoria, conexionBiblioteca)

        Dim dataReaderLibros As SqlDataReader = commandoGetLibros.ExecuteReader()

        While dataReaderLibros.Read
            listaLibrosPorCategoria.Add(New Libro(dataReaderLibros("Isbn"), dataReaderLibros("CodCategoria"), Integer.Parse(dataReaderLibros("CodSoporte")), dataReaderLibros("Titulo"), dataReaderLibros("Ejemplares")))
        End While
        CerrarConexion()
        Return listaLibrosPorCategoria.AsReadOnly

    End Function

    Public Function getLibrosPorTitulo(tituloLibro As Libro) As ObjectModel.ReadOnlyCollection(Of Libro)
        AbrirConexion()
        Dim listaLibrosPorCategoria As New List(Of Libro)

        Dim sqlSelectLibroPorCategoria As String = "SELECT * FROM Libro WHERE Libro.Titulo ='" & tituloLibro.Titulo & "'"

        Dim commandoGetLibros As New SqlCommand(sqlSelectLibroPorCategoria, conexionBiblioteca)

        Dim dataReaderLibros As SqlDataReader = commandoGetLibros.ExecuteReader()

        While dataReaderLibros.Read
            listaLibrosPorCategoria.Add(New Libro(dataReaderLibros("Isbn"), dataReaderLibros("CodCategoria"), Integer.Parse(dataReaderLibros("CodSoporte")), dataReaderLibros("Titulo"), dataReaderLibros("Ejemplares")))
        End While
        CerrarConexion()
        Return listaLibrosPorCategoria.AsReadOnly

    End Function

    Public Sub PrestarLibro(miPrestamo As Prestamo)
        AbrirConexion()
        Dim listaPrestados As List(Of Prestamo) = getPrestamos().ToList

        Dim sqlInsertPrestamo As String = "INSERT INTO Prestamo VALUES('" & miPrestamo.Isbn & "','" & miPrestamo.Dni & "','" & miPrestamo.FechaPrestamo & "','" & miPrestamo.FechaDevolucion & "'," & miPrestamo.Entregado & ")"

        Dim sqlUpdateEjemplares As String = "UPDATE Libro SET Ejemplares=Ejemplares-1 WHERE Isbn='" & miPrestamo.Isbn & "'"

        Dim comandoUpdateEjemplares As New SqlCommand(sqlUpdateEjemplares, conexionBiblioteca)

        Dim comandoInsertPrestamo As New SqlCommand(sqlInsertPrestamo, conexionBiblioteca)

        comandoUpdateEjemplares.ExecuteNonQuery()
        comandoInsertPrestamo.ExecuteNonQuery()
        CerrarConexion()
    End Sub
    Public Function getMorosos() As ObjectModel.ReadOnlyCollection(Of Cliente)
        AbrirConexion()

        Dim listaMorosos As New List(Of Cliente)

        Dim sqlSelectMorosos As String = "SELECT * FROM (Cliente INNER JOIN Prestamo ON Cliente.Dni = Prestamo.Dni) WHERE Prestamo.Entregado=0"

        Dim commandoGetMorosos As New SqlCommand(sqlSelectMorosos, conexionBiblioteca)

        Dim dataReaderMorosos As SqlDataReader = commandoGetMorosos.ExecuteReader()

        While dataReaderMorosos.Read
            'Bucle para validar la fecha y si se ha pasado de fecha meterlo en la lista de morosos
            For i As Integer = 0 To listaPrestamos.Count - 1
                If (listaPrestamos(i).Dni = dataReaderMorosos("Dni")) Then
                    Dim fecha As String() = listaPrestamos(i).FechaDevolucion.Split("/")

                    If (Integer.Parse(fecha(2)) < (Today.Year)) Then
                        listaMorosos.Add(New Cliente(dataReaderMorosos("Isbn"), dataReaderMorosos("Nombre"), dataReaderMorosos("Apellidos"), dataReaderMorosos("Telefono")))
                        Exit For
                    Else
                        If (Integer.Parse(fecha(1) - 1) < (Today.Month)) Then
                            listaMorosos.Add(New Cliente(dataReaderMorosos("Isbn"), dataReaderMorosos("Nombre"), dataReaderMorosos("Apellidos"), dataReaderMorosos("Telefono")))
                            Exit For
                        Else
                            If (Integer.Parse(fecha(0)) < (Today.Day)) Then
                                listaMorosos.Add(New Cliente(dataReaderMorosos("Isbn"), dataReaderMorosos("Nombre"), dataReaderMorosos("Apellidos"), dataReaderMorosos("Telefono")))
                                Exit For
                            End If
                        End If
                    End If
                End If
            Next
        End While
        CerrarConexion()
        Return listaMorosos.AsReadOnly
    End Function

    Public Function getClientesPorNombreLibroPrestado(libro As Libro) As ObjectModel.ReadOnlyCollection(Of Cliente)
        AbrirConexion()
        Dim listaclientes As New List(Of Cliente)

        Dim sqlSelectClientesPorLibroPrestado As String = "SELECT * FROM (Cliente INNER JOIN Prestamo ON Cliente.Dni = Prestamo.Dni) INNER JOIN Libro ON Prestamo.Isbn = Libro.Isbn WHERE Libro.Titulo='" & libro.Titulo & "'"

        Dim commandoSelectClientes As New SqlCommand(sqlSelectClientesPorLibroPrestado, conexionBiblioteca)

        Dim dataReaderClientesConEjemplar As SqlDataReader = commandoSelectClientes.ExecuteReader()

        While dataReaderClientesConEjemplar.Read
            listaclientes.Add(New Cliente(dataReaderClientesConEjemplar("Dni"), dataReaderClientesConEjemplar("Nombre"), dataReaderClientesConEjemplar("Apellidos"), dataReaderClientesConEjemplar("Telefono")))
        End While

        CerrarConexion()
        Return listaclientes.AsReadOnly
    End Function
    Public Sub DevolverLibro(miPrestamo As Prestamo)
        Dim listaPrestamos As List(Of Prestamo) = getPrestamos.ToList
        AbrirConexion()

        Dim sqlUpdateEjemplares As String = "UPDATE Prestamo SET Prestamo.Entregado=1 WHERE Isbn='" & miPrestamo.Isbn & "'"

        Dim comandoUpdateEjemplares As New SqlCommand(sqlUpdateEjemplares, conexionBiblioteca)

        comandoUpdateEjemplares.ExecuteNonQuery()
        CerrarConexion()
        inicializarListaPrestamos()
    End Sub
    Public Function getTituloLibroPorPrestamo(prestamo As Prestamo) As String
        AbrirConexion()
        Dim milibro As Libro
        Dim sqlSelectTituloPorPrestamo As String = "SELECT * FROM Libro INNER JOIN Prestamo ON Libro.Isbn = Prestamo.Isbn WHERE Prestamo.Isbn='" & prestamo.Isbn & "'"

        Dim commandoSelectClientes As New SqlCommand(sqlSelectTituloPorPrestamo, conexionBiblioteca)

        Dim dataReaderClientesConEjemplar As SqlDataReader = commandoSelectClientes.ExecuteReader()

        While dataReaderClientesConEjemplar.Read
            milibro = New Libro(dataReaderClientesConEjemplar("Isbn"), dataReaderClientesConEjemplar("CodCategoria"), Integer.Parse(dataReaderClientesConEjemplar("CodSoporte")), dataReaderClientesConEjemplar("Titulo"), dataReaderClientesConEjemplar("Ejemplares"))
            If IsNothing(milibro) Then
                Return "Error al insertar"
                Exit Function
            End If
        End While
        CerrarConexion()
        Return milibro.Titulo
    End Function
#Region "Inserts y updates"

    Public Function insertarLibro(libro As Libro, listAutores As List(Of Autor))
        AbrirConexion()
        Dim sqlInsertLibro As String = "INSERT INTO Libro VALUES('" & libro.Isbn & "'," & libro.CodCategoria & "," & libro.CodSoporte & ",'" & libro.Titulo & "'," & libro.Ejemplares & ")"

        Dim comandoInsertLibro As New SqlCommand(sqlInsertLibro, conexionBiblioteca)
        comandoInsertLibro.ExecuteNonQuery()
        For i As Integer = 0 To listAutores.Count - 1
            Dim sqlInsertAutor_libro As String = "INSERT INTO AutorLibro VALUES(" & listAutores(i).CodAutor & ",'" & libro.Isbn & "')"
            Dim comandoInsertAutorLibro As New SqlCommand(sqlInsertAutor_libro, conexionBiblioteca)
            comandoInsertAutorLibro.ExecuteNonQuery()
        Next

        CerrarConexion()
        Return "Libro insertado"
    End Function
#End Region
    'Public Sub Altas()

    'End Sub
End Class
